USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetOpportunityProducts')
	BEGIN
		DROP  Procedure  dbo.GetOpportunityProducts
	END

GO

CREATE Procedure dbo.GetOpportunityProducts
	@PKID	VARCHAR(100)
AS
BEGIN

	SELECT 
		p.ProductDesc									AS [Product Name]
	,	o.Quantity										AS [Quantity]
	,	p.UnitPrice										AS [Unit Price]
	,	o.CustomPrice									AS [Custom Price]
	,	o.Quantity * ISNULL(o.CustomPrice, p.UNitPrice)	AS [Total]
	,	o.OpportunityID									AS [PKID]
	FROM TR_PRODUCT p WITH (NOLOCK)
	INNER JOIN TR_OPPORTUNITYI as o WITH (NOLOCK)
		ON o.ProductID = p.ProductID
	WHERE
		CAST(o.OppGuid AS VARCHAR(40)) = ISNULL(@PKID,'')
	ORDER BY ProductDesc ASC

END
GO